/**
 * @Package: 	com.group.DAO 
 * @author: 	Liu  
 * @date: 		Dec 28, 2018 9:41:05 AM 
 */
package com.group.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.group.C3P0Utils.C3P0Utils;
import com.group.pojo.GEvaluate;
import com.group.pojo.GWork;

/**
 * @ClassName:	 GWorkDAO.java
 * @Description: 任务DAO
 * @author:		 Liu
 * @date: 		 Dec 28, 2018 9:41:05 AM 
 */
public class GWorkDAO {
	public List<GWork> getGWorkList(){
		List <GWork> work_list = new ArrayList<GWork>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM  g_work";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GWork work = new GWork();
				GEvaluate eva = new GEvaluate();
				work.setWorkID(rs.getInt("work_id"));
				work.setWorkTitle(rs.getString("work_title"));
				work.setWorkContent(rs.getString("work_content"));
				work.setWorkBegin(rs.getString("work_begin"));
				work.setWorkEnd(rs.getString("work_end"));
				work.setOpenEva(isPassed(rs.getString("work_end")));
				List<String> groupList = eva.getGroupListByWorkID(rs.getInt("work_id"));
				work.setGroupList(groupList);
				work_list.add(work);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return work_list;
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 发布任务
	 * @param:		   任务类
	 * @return:		  
	 * @author: 	 Liu
	 * @date:		 Dec 30, 2018 9:59:34 PM 
	 */
	public void addWork(GWork w, String[] groupID) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "INSERT INTO g_work(work_title, work_content, work_end)VALUES(?,?,?)";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, w.getWorkTitle());
			pstmt.setString(2, w.getWorkContent());
			pstmt.setString(3, w.getWorkEnd());
			pstmt.executeUpdate();
			if(groupID != null) {
				for(String g:groupID) {
					int workID = w.getTheLastestWorkID();
					GEvaluate eva = new GEvaluate(workID, Integer.parseInt(g));
					eva.addEva(eva);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 获取最新发布任务的ID(暂时不用)
	 * @param:		   参数
	 * @return:		   任务ID
	 * @author: 	 Liu
	 * @date:		 Dec 31, 2018 3:23:38 PM 
	 */
	public int getTheLastestWorkID() {
		int workID = 999;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM  g_work order by work_id desc limit 1";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				workID = rs.getInt("work_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return workID;
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 获取任务信息
	 * @param:		   任务ID
	 * @return:		   任务类
	 * @author: 	 Liu
	 * @date:		 Dec 31, 2018 3:56:31 PM 
	 */
	public GWork getGWorkInfoByID(int workID) {
		GWork w = new GWork();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM  g_work WHERE work_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, workID);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GEvaluate eva = new GEvaluate();
				w.setWorkID(rs.getInt("work_id"));
				w.setWorkTitle(rs.getString("work_title"));
				w.setWorkContent(rs.getString("work_content"));
				w.setWorkBegin(rs.getString("work_begin"));
				w.setWorkEnd(rs.getString("work_end"));
				List<String> groupList = eva.getGroupListByWorkID(rs.getInt("work_id"));
				w.setGroupList(groupList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return w;
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 编辑任务信息
	 * @param:		  任务，小组ID列表
	 * @return:		  
	 * @author: 	 Liu
	 * @date:		 Dec 31, 2018 7:20:13 PM 
	 */
	public void updateWorkInfo(GWork w, String[] groupID) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "UPDATE g_work SET work_title = ?, work_content = ?, work_end = ? WHERE work_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, w.getWorkTitle());
			pstmt.setString(2, w.getWorkContent());
			pstmt.setString(3, w.getWorkEnd());
			pstmt.setInt(4, w.getWorkID());
			pstmt.executeUpdate();
			GEvaluate e = new GEvaluate();
			e.delEva(w.getWorkID());
			if(groupID != null) {
				for(String g:groupID) {
					//int workID = w.getTheLastestWorkID();
					GEvaluate eva = new GEvaluate(w.getWorkID(), Integer.parseInt(g));
					eva.addEva(eva);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 删除任务
	 * @param:		   任务ID
	 * @return:		   
	 * @author: 	 Liu
	 * @date:		 Dec 31, 2018 8:35:55 PM 
	 */
	public void deleteWorkByID(int workID) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "DELETE FROM g_work where work_id= ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, workID);
			pstmt.executeUpdate();
			GEvaluate e = new GEvaluate();
			e.delEva(workID);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GWorkDAO.java
	 * @Description: 判断任务是否过期
	 * @param:		   任务结束时间
	 * @return:		   过期(true)/未过期(false)
	 * @author: 	 Liu
	 * @date:		 Jan 2, 2019 8:08:06 AM 
	 */
	public boolean isPassed(String workEnd) {
		boolean flag = false;//true为任务已过期
		//获取当前时间
		Date date = new Date();
		DateFormat dateF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String currentTime = dateF.format(date);
		try{
			Date workend = dateF.parse(workEnd);
			Date currenttime = dateF.parse(currentTime);
			if(workend.getTime() > currenttime.getTime()){
				flag = false;
			}else if(workend.getTime() < currenttime.getTime()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return flag;
	}
}
